package com.Web.DAO.Impl;

import com.Web.DAO.IClassDAO;
import com.Web.Person.BanJi;
import com.Web.util.JDBCUtil;
import com.Web.util.PageInfo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ClassDAOImpl implements IClassDAO {

    @Override
    public List<BanJi> selectAll() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<BanJi> list = new ArrayList<>();
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT id,name,address FROM banji";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String address = resultSet.getString("address");
                BanJi banji = new BanJi(id,name,address);
                list.add(banji);
            }
            for (BanJi banji : list) {
                System.out.println(banji);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtil.close(connection,preparedStatement,resultSet);
        }
        return list;
    }

    @Override
    public void deleteById(int i) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "DELETE FROM banji WHERE id=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,i);
            System.out.println(preparedStatement);
            int count = preparedStatement.executeUpdate();
            System.out.println("count: " + count);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtil.close(connection,preparedStatement,null);
        }
    }

    @Override
    public void add(BanJi banji) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "INSERT INTO banji(name,address) VALUES(?,?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,banji.getName());
            preparedStatement.setString(2,banji.getAddress());
            System.out.println(preparedStatement);
            int count = preparedStatement.executeUpdate();
            System.out.println("count：" + count);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtil.close(connection,preparedStatement,null);
        }
    }

    @Override
    public BanJi toUpdate(int id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        BanJi banji = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT id,name,address FROM banji WHERE id=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                String name = resultSet.getString("name");
                String address = resultSet.getString("address");
                banji = new BanJi(id, name, address);
            }
            System.out.println(banji);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtil.close(connection,preparedStatement,resultSet);
        }
        return banji;
    }

    @Override
    public void update(BanJi banji) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "UPDATE banji SET name=?,address=? WHERE id=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, banji.getName());
            preparedStatement.setString(2, banji.getAddress());
            preparedStatement.setInt(3,banji.getId());
            System.out.println(preparedStatement);
            int count = preparedStatement.executeUpdate();
            System.out.println("count：" + count);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtil.close(connection,preparedStatement,null);
        }
    }

    @Override
    public List<BanJi> selectByPage(Integer offset, Integer pageSize) {
        System.out.println("selectByPage");
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<BanJi> list = new ArrayList<>();
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT id,name,address FROM banji LIMIT ?,?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,offset);
            preparedStatement.setInt(2,pageSize);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String address = resultSet.getString("address");
                BanJi banji = new BanJi(id,name,address);
                list.add(banji);
            }
            for(BanJi banji : list) {
                System.out.println(banji);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtil.close(connection,preparedStatement,resultSet);
        }
        return list;
    }

    @Override
    public Integer selectTotalCount() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Integer totalCount = 0;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "SELECT count(*) FROM banji";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()) {
                totalCount = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtil.close(connection,preparedStatement,resultSet);
        }
        return totalCount;
    }
}
